var mysql=require("mysql");
var crypto = require('./crypto');

var pool = null;

function nop(a,b,c,d,e,f,g){

}

function query(sql,callback){
    pool.getConnection(function(err,conn){
        if(err){
            callback(err,null,null);
        }else{
            conn.query(sql,function(qerr,vals,fields){
                //释放连接  
                conn.release();
                //事件驱动回调  
                callback(qerr,vals,fields);
            });
        }
    });
};


let query2 = function( sql, values ) {
    return new Promise(( resolve, reject ) => {
        pool.getConnection(function(err, connection) {
        if (err) {
            reject( err )
        } else {
            connection.query(sql, values, ( err, rows) => {

                if ( err ) {
                    console.log(err);
                    reject( err );
                } else {
                    resolve( rows );
                }
                connection.release()
        })
        }
    })
})
}

exports.init = function(config){
    pool = mysql.createPool({
        host: config.HOST,
        user: config.USER,
        password: config.PSWD,
        database: config.DB,
        port: config.PORT,
    });
};

//分页获取
exports.get_trip = function(end,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT trip.*,user_accounts.nickName,user_accounts.avatarUrl FROM trip,user_accounts where trip.user_id=user_accounts.id and trip.id < "'+end+'" order by trip.id desc limit 20';

    if(end == 0){
        sql = 'SELECT trip.*,user_accounts.nickName,user_accounts.avatarUrl FROM trip ,user_accounts where trip.user_id=user_accounts.id order by trip.id desc limit 20';
    }

    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows);
    });
};


exports.getByToken =async function(token){
    var sql = 'SELECT * FROM user_token where token = "'+token+'"';
    let dataList = await query2( sql );
    return dataList[0];
};

//保存token
exports.saveToken =async function(token){
    var sql = 'insert into user_token(userId,openId,token) values("'+token.userId+'","'+token.openId+'","'+token.token+'") ON DUPLICATE KEY UPDATE token = "'+token.token+'"';
    let dataList = await query2( sql );
    return dataList[0];
};


//获取用户基本信息
exports.getUserById =async function(id){
    var sql = 'SELECT * FROM user_accounts where id = "'+id+'"';
    let dataList = await query2( sql );
    return dataList[0];
};

//获取用户基本信息
exports.getUserByOpenId =async function(openId){
    var sql = 'SELECT * FROM user_accounts where openId = "'+openId+'"';
    let dataList = await query2( sql );
    return dataList[0];
};

//获取用户基本信息
exports.saveUser =async function(user){
    console.log(user);
    var sql = 'insert ignore into user_accounts(openId,nickName,language,city,province,country,avatarUrl,gender,create_time) ' +
        'values("'+user.openId+'","'+user.nickName+'","'+user.language+'","'+user.city+'","'+user.province+'","'+user.country+'","'+user.avatarUrl+'","'+user.gender+'",unix_timestamp(now())*1000) ';
    console.log(sql);
    let data = await query2( sql );
    return data;
};


//获取粉丝数
exports.getFansCountById =async function(id){
    var sql = 'SELECT count(*) as count FROM guanzhu where user_id_2 = "'+id+'"';
    let dataList = await query2( sql );
    return dataList[0].count;
};

//获取关注数
exports.getFollowerCountById = async function(id){
    var sql = 'SELECT count(*) as count FROM guanzhu where user_id_1 = "'+id+'"';
    let dataList = await query2( sql );
    return dataList[0].count;
};
//根据用户id获取旅行列表
exports.getTripByUserId = function(userId,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT * FROM trip where trip.user_id="'+userId+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows);
    });
};

exports.updateViewCount = function(tripId){
    var sql = 'update trip set view_count=view_count+1 where id="'+tripId+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            // throw err;
        }
    });
};



//获取基本信息
exports.getTripById = function(id,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT * FROM trip,user_accounts where trip.user_id=user_accounts.id and trip.id = "'+id+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows[0]);
    });
};

//获取足迹列表
exports.getWayPointByTripId = function(tripId,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT * FROM waypoint where trip_id = "'+tripId+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows);
    });
};

//获取足迹单个信息
exports.getWayPointByWayPointId = function(id,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT * FROM waypoint where id = "'+id+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows[0]);
    });
};

//获取评论列表信息
exports.getCommentsByWPId = function(waypointId,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT cm.*,ua.avatarUrl,ua.nickName FROM comments as cm,user_accounts as ua where cm.user_id=ua.id and waypoint_id = "'+waypointId+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows);
    });
};

//获取喜欢（点赞）列表信息 enshrine 收藏
exports.getEnshrineByWPId = function(waypointId,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT * FROM shoucang,user_accounts where shoucang.user_id=user_accounts.id and waypoint_id = "'+waypointId+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows);
    });
};


//获取基本信息
exports.getCommentsCountByWPId = function(waypointId,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT count(*) as count FROM comments where waypoint_id = "'+waypointId+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows[0]);
    });
};

//获取基本信息
exports.getCommentsCountByTripId2=async function getCount(tripId){
     var sql = 'SELECT count(*) as count FROM comments where waypoint_id in(select id from waypoint where trip_id="'+tripId+'")';
        let dataList = await query2( sql );
        return dataList[0].count;
};

//获取基本信息
exports.getCommentsCountByTripId = function(tripId,callback){

    callback = callback == null? nop:callback;
    var sql = 'SELECT count(*) as count FROM comments where waypoint_id in(select id from waypoint where trip_id="'+tripId+'")';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows[0]);
    });
};


//获取基本信息 enshrine 收藏
exports.getEnshrineCountByWPId = function(waypointId,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT count(*) as count FROM shoucang where waypoint_id = "'+waypointId+'"';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows[0]);
    });
};

exports.getEnshrineCountByTripId2 = async function(tripId){
    var sql = 'SELECT count(*) as count FROM shoucang where waypoint_id in(select id from waypoint where trip_id="'+tripId+'")';
    let dataList = await query2( sql );
    return dataList[0].count;
};
//获取基本信息 enshrine 收藏
exports.getEnshrineCountByTripId = function(tripId,callback){
    callback = callback == null? nop:callback;
    var sql = 'SELECT count(*) as count FROM shoucang where waypoint_id in(select id from waypoint where trip_id="'+tripId+'")';
    query(sql, function(err, rows, fields) {
        if (err) {
            callback(null);
            throw err;
        }
        callback(rows[0]);
    });
};

// 足迹点赞
exports.addShouCang=function(userId,waypointId,callback){
    callback = callback == null? nop:callback;
    var sql = 'insert ignore into shoucang(user_id,waypoint_id,create_time) values("'+userId+'","'+waypointId+'",unix_timestamp(now())*1000)';
    query(sql, function(err, rows, fields) {
        if (err) {
            if(err.code == 'ER_DUP_ENTRY'){
                callback(false);
                return;
            }
            callback(false);
            throw err;
        }
        else{
            callback(true);
        }
    });
};

// 关注
exports.addGuanZhu=function(userId1,userId2,callback){
    callback = callback == null? nop:callback;
    var sql = 'insert ignore into guanzhu(user_id_1,user_id_2,create_time) values("'+userId1+'","'+userId2+'",unix_timestamp(now())*1000)';
    query(sql, function(err, rows, fields) {
        if (err) {
            if(err.code == 'ER_DUP_ENTRY'){
                callback(false);
                return;
            }
            callback(false);
            throw err;
        }
        else{
            callback(true);
        }
    });
};


// 评论
exports.addPingLun=function(userId,waypointId,comment,callback){
    callback = callback == null? nop:callback;
    var sql = 'insert ignore into comments(user_id,waypoint_id,comments,create_time) values("'+userId+'","'+waypointId+'","'+comment+'",unix_timestamp(now())*1000)';
    query(sql, function(err, rows, fields) {
        if (err) {
            if(err.code == 'ER_DUP_ENTRY'){
                callback(false);
                return;
            }
            callback(false);
            throw err;
        }
        else{
            callback(true);
        }
    });
};

exports.addTrip=function(trip,callback){
    callback = callback == null? nop:callback;
    var sql = 'insert into trip(title,start_date,end_date,cover_image_w640,popular_place_str,create_time) values("'
        +trip.title+'","'+trip.start_date+'","'+trip.end_date+'","'+trip.cover_image_w640+'","'+trip.popular_place_str+'",unix_timestamp(now())*1000)';
    query(sql, function(err, rows, fields) {
        if (err) {
            if(err.code == 'ER_DUP_ENTRY'){
                callback(false);
                return;
            }
            callback(false);
            throw err;
        }
        else{
            callback(true);
        }
    });
};

exports.addWaypoint=function(waypoint,callback){
    callback = callback == null? nop:callback;
    var sql = 'insert into waypoit(trip_id,trip_date,photo_info_h,photo_info_w,text,photo_webtrip,create_time) values("'
        +trip.trip_id+'","'+trip.trip_date+'","'+trip.photo_info_h+'","'+trip.photo_info_w+'","'+trip.text+'","'+trip.photo_webtrip+'",unix_timestamp(now())*1000)';
    query(sql, function(err, rows, fields) {
        if (err) {
            if(err.code == 'ER_DUP_ENTRY'){
                callback(false);
                return;
            }
            callback(false);
            throw err;
        }
        else{
            callback(true);
        }
    });
};


exports.query = query;